#include "database_manager.h"
#include <fstream>   // IO
#include <ctime>     // 时间
#include <stdexcept> // 异常处理头文件
#include <cstring>
#include <iostream>

#define ERR_LOG(msg)                                     \
    do                                                   \
    {                                                    \
        cerr << __FILE__ << __func__ << __LINE__ << endl \
             << msg << endl;                             \
    } while (0)

#define DICT_PATH "./dict.txt"
// 返回值：runtime_error
DatabaseManager::DatabaseManager(const std::string &user_db_name, const std::string &dict_db_name)
{
    if (sqlite3_open(user_db_name.c_str(), &user_db_) != SQLITE_OK)
    {
        throw runtime_error(
            "用户数据库打开失败" + string(sqlite3_errmsg(user_db_)));
    }

    if (sqlite3_open(dict_db_name.c_str(), &dict_db_) != SQLITE_OK)
    {
        throw runtime_error(
            "单词数据库打开失败" + string(sqlite3_errmsg(dict_db_)));
    }
}

/*
    关闭数据库的连接
*/
DatabaseManager::~DatabaseManager()
{
    sqlite3_close(user_db_);
    sqlite3_close(dict_db_);
}

// 将账号和密码存放到数据表中, 但是要注意插入之前用户名不能存在
bool DatabaseManager::registerUser(const string &name, const string &password)
{
    unique_lock<mutex> lock(user_mutex_);

    const char *sql = "insert into User values(?,?,0);";
    sqlite3_stmt *stmt = NULL;

    if (sqlite3_prepare_v2(user_db_, sql, -1, &stmt, NULL) != SQLITE_OK)
    {
        ERR_LOG("sqlite3_prepare_v2 error");
        cerr << sqlite3_errmsg(user_db_) << endl;
        return false;
    }
    // 绑定参数
    sqlite3_bind_text(stmt, 1, name.c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, password.c_str(), -1, SQLITE_STATIC);

    // 执行插入操作
    int result = sqlite3_step(stmt);
    sqlite3_finalize(stmt);

    // 用户名已经存在：注册失败，用户名不存在：注册成功
    if (result == SQLITE_CONSTRAINT)
    {
        return false;
    }

    return result == SQLITE_DONE;
}

bool DatabaseManager::loginUser(const string &name, const string &password, bool &is_online)
{
    unique_lock<mutex> lock(user_mutex_);

    const char *sql = "select status from User where name=? and password =?;";

    sqlite3_stmt *stmt = NULL;
    if (sqlite3_prepare_v2(user_db_, sql, -1, &stmt, NULL) != SQLITE_OK)
    {
        ERR_LOG("sqlite3_prepare_v2 error");
        cerr << sqlite3_errmsg(user_db_) << endl;
        return false;
    }

    // 绑定参数
    sqlite3_bind_text(stmt, 1, name.c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, password.c_str(), -1, SQLITE_STATIC);

    int result = sqlite3_step(stmt);

    // 有数据
    if (result == SQLITE_ROW)
    {
        // 将第0个参数转换成int后返回给is_online
        is_online = sqlite3_column_int(stmt, 0);

        sqlite3_finalize(stmt);

        // 如果是离线状态，设置为在线状态
        if (is_online == 0)
        {

            const char *updata_sql = "update User set status=1 where name=?;";

            if (sqlite3_prepare_v2(user_db_, updata_sql, -1, &stmt, NULL) != SQLITE_OK)
            {
                ERR_LOG("sqlite3_prepare_v2 error");
                cerr << sqlite3_errmsg(user_db_) << endl;
                return false;
            }

            sqlite3_bind_text(stmt, 1, name.c_str(), -1, SQLITE_STATIC);

            int result = sqlite3_step(stmt);
            sqlite3_finalize(stmt);
            return true;
        }
        else // 如果是在线状态
        {
            return false;
        }
    }

    // 查询失败，没有行数据，登录失败
    sqlite3_finalize(stmt);
    return false;
}

bool DatabaseManager::logoutUser(const string &name)
{
    unique_lock<mutex> lock(user_mutex_);
    const char *sql = "update User set status=0 where name=?;";
    sqlite3_stmt *stmt = NULL;

    // 预处理
    if (sqlite3_prepare_v2(user_db_, sql, -1, &stmt, NULL) != SQLITE_OK)
    {
        ERR_LOG("sqlite3_prepare_v2 error");
        cerr << sqlite3_errmsg(user_db_) << endl;
        return false;
    }

    sqlite3_bind_text(stmt, 1, name.c_str(), -1, NULL);

    int result = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return result == SQLITE_DONE;
}

bool DatabaseManager::initTables()
{
    return initUserDataBase() && initDiaDataBase();
}

bool DatabaseManager::initDiaDataBase()
{
    // 对dict_db_进行操作，加锁
    unique_lock<mutex> lock(dict_mutex_);
    const char *sql = "create table if not exists Dict("
                      "word text,"
                      "mean text"
                      ");";

    char *errmsg = NULL;
    if (sqlite3_exec(dict_db_, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("单词数据表创建失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        return false;
    }

    // 检查词典数据库是否为空
    sql = "select count(*) from Dict;";
    bool need_import = false;

    if (sqlite3_exec(dict_db_, sql, [](void *statue, int, char **data, char **) -> int
                     {
        if(strcmp("0", *data) == 0)
        {
            *(bool *)statue = true; // 需要导入数据
        }
        return 0; }, &need_import, &errmsg) != SQLITE_OK)
    {
        printf("判断数据库为空失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        return false;
    }

    {
        // sqlite3_stmt *stmt; // 接收预处理sql的结果

        //
        // // 表示执行成功
        // if(sqlite3_prepare_v2(dict_db_, sql, -1, &stmt, NULL) == SQLITE_ROW)
        // {
        //     if(sqlite3_column_int(stmt, 0) == 0) // 数据为空
        //     {
        //         need_import = true;
        //     }
        // }
        // else
        //     return false;

        // // 释放预编译的信息
        // sqlite3_finalize(stmt);
    }

    return need_import ? dicToDataBase() : true;
}

bool DatabaseManager::initUserDataBase()
{
    unique_lock<mutex> lock(user_mutex_);

    const char *sql =
        "create table if not exists User(" // 创建用户表
        "name text primary key,"           // 用户名，作为主键
        "password text,"                   // 密码
        "status int"                       // 用户状态 0表示离线，1表示在线
        ");"
        "create table if not exists History(" // 创建历史记录表
        "name text,"                          // 用户的昵称
        "word text,"                          // 英文
        "mean text,"                          // 翻译
        "time text"                           // 时间
        ");";

    char *errmsg = NULL;
    if (sqlite3_exec(user_db_, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("用户数据表初始化失败: %s\n", errmsg);
        // 释放错误信息
        sqlite3_free(errmsg);
        return false;
    }

    // 重置所有用户的状态为离线
    sql = "update User set status=0;";
    if (sqlite3_exec(user_db_, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("用户状态重置失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        return false;
    }

    return true;
}

bool DatabaseManager::dicToDataBase()
{
    ifstream ifs(DICT_PATH);

    if (!ifs)
    {
        cerr << "无法打开词典文件: " << DICT_PATH << endl;
        return false;
    }

    // 预处理需要使用的sql语句
    const char *sql = "insert into Dict values(?,?);";

    string line; // 用于读取文件中的一行数据
    while (getline(ifs, line))
    {
        // // 分割单词和翻译
        size_t pod = line.find(' ');

        if (pod == string::npos)
        {
            cerr << "无效的词典条目(单词 翻译): " << line << endl;
            continue;
        }
        // 分割字符串, 截取单词
        string word = line.substr(0, pod);
        // 截取翻译
        string mean = "";
        for (int i = pod + 1, x = 0; i < line.size(); i++)
        {
            if (line[i] != ' ')
                x = 1;
            if (x == 1)
                mean.push_back(line[i]);
        }
        // 绑定参数
        sqlite3_stmt *stmt = NULL;

        // 预处理命令
        if (sqlite3_prepare_v2(dict_db_, sql, -1, &stmt, NULL) != SQLITE_OK)
        {
            cerr << "sql 准备失败" << sqlite3_errmsg(dict_db_) << endl;
            return false;
        }

        sqlite3_bind_text(stmt, 1, word.c_str(), -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 2, mean.c_str(), -1, SQLITE_STATIC);

        // 执行失败
        if (sqlite3_step(stmt) != SQLITE_DONE)
        {
            cerr << "数据库插入失败" << sqlite3_errmsg(dict_db_) << endl;
            sqlite3_finalize(stmt);
            return false;
        }
        sqlite3_finalize(stmt);
    }
    return true;
}

bool DatabaseManager::queryWord(const string &word, string &meaning)
{
    // 加锁
    unique_lock<mutex> lock(dict_mutex_);

    const char *sql = "select mean from Dict where word = ?;";
    sqlite3_stmt *stmt = NULL;

    // 查找预处理
    if (sqlite3_prepare_v2(dict_db_, sql, -1, &stmt, NULL) != SQLITE_OK)
    {
        cerr << "查找预处理失败" << sqlite3_errmsg(dict_db_) << endl;
        sqlite3_finalize(stmt);
        return false;
    }

    sqlite3_bind_text(stmt, 1, word.c_str(), -1, SQLITE_STATIC);

    int result = sqlite3_step(stmt);

    // 正常执行
    if (result == SQLITE_ROW)
    {
        meaning = string(reinterpret_cast<const char *>(sqlite3_column_text(stmt, 0)));

        sqlite3_finalize(stmt);
        if (meaning.empty())
            return false;
        return true;
    }
    sqlite3_finalize(stmt);

    return false;
}

bool DatabaseManager::recordHistory(const string &name, const string &word, const string &meading, const string &time)
{
    unique_lock<mutex> lock(user_mutex_);

    const char *sql = "insert into History values(?,?,?,?);";

    sqlite3_stmt *stmt = NULL;

    if (sqlite3_prepare_v2(user_db_, sql, -1, &stmt, NULL) != SQLITE_OK)
    {
        cerr << "添加历史记录：预处理失败" << sqlite3_errmsg(dict_db_) << endl;
        sqlite3_finalize(stmt);
        return false;
    }

    // 绑定参数
    sqlite3_bind_text(stmt, 1, name.c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, word.c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 3, meading.c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 4, time.c_str(), -1, SQLITE_STATIC);

    int result = sqlite3_step(stmt);

    sqlite3_finalize(stmt);
    return result == SQLITE_DONE;
}

bool DatabaseManager::getHistory(const string &name, string &history)
{
    unique_lock<mutex> lock(user_mutex_);

    const char *sql = "select word,mean,time from History where name=? order by time DESC;";
    sqlite3_stmt * stmt = NULL;

    if(sqlite3_prepare_v2(user_db_, sql, -1, &stmt, NULL) != SQLITE_OK)
    {
        cerr << "查找历史记录：预处理失败" << sqlite3_errmsg(dict_db_) << endl;
        sqlite3_finalize(stmt);
        return false;
    }

    sqlite3_bind_text(stmt, 1, name.c_str(), -1, SQLITE_STATIC);

    history.clear();

    while(sqlite3_step(stmt) == SQLITE_ROW)
    {   // 获取参数
        const char *word = reinterpret_cast<const char *>(sqlite3_column_text(stmt, 0));
        const char *mean = reinterpret_cast<const char *>(sqlite3_column_text(stmt, 1));
        const char *time = reinterpret_cast<const char *>(sqlite3_column_text(stmt, 2));

        if(word && mean && time)
        {
            history += string(word) + '\t';
            history += string(mean) + '\t';
            history += string(time) + '\n';
        }
    }

    sqlite3_finalize(stmt);
    return true;
}